OptExpiryDefinition

V8 Message Definiton

This table maps option root/expiration combinations to their deliverable future. Mappings are sourced fromm listing exchange product definitions.\nBaseObj:Root

METADATA

Attribute	Value
Topic	4335-product-definition
MLink Token	OptionDefinition
Product	SRAnalytics
accessType	SELECT

Table Definition

Field	Type	Key	Default Value	Comment
ekey_at	enum - AssetType	PRI	'None'	option rootexpiration
ekey_ts	enum - TickerSrc	PRI	'None'	option rootexpiration
ekey_tk	VARCHAR(12)	PRI	''	option rootexpiration
ekey_yr	SMALLINT UNSIGNED	PRI	0	option rootexpiration
ekey_mn	TINYINT UNSIGNED	PRI	0	option rootexpiration
ekey_dy	TINYINT UNSIGNED	PRI	0	option rootexpiration
ticker_at	enum - AssetType		'None'	master ticker
ticker_ts	enum - TickerSrc		'None'	master ticker
ticker_tk	VARCHAR(12)		''	master ticker
fkey_at	enum - AssetType		'None'	settlement future if any
fkey_ts	enum - TickerSrc		'None'	settlement future if any
fkey_tk	VARCHAR(12)		''	settlement future if any
fkey_yr	SMALLINT UNSIGNED		0	settlement future if any
fkey_mn	TINYINT UNSIGNED		0	settlement future if any
fkey_dy	TINYINT UNSIGNED		0	settlement future if any
uPrcDriverKey_at	enum - AssetType		'None'	underlier price driver for this option expiry default is fkey if it exists
uPrcDriverKey_ts	enum - TickerSrc		'None'	underlier price driver for this option expiry default is fkey if it exists
uPrcDriverKey_tk	VARCHAR(12)		''	underlier price driver for this option expiry default is fkey if it exists
uPrcDriverKey_yr	SMALLINT UNSIGNED		0	underlier price driver for this option expiry default is fkey if it exists
uPrcDriverKey_mn	TINYINT UNSIGNED		0	underlier price driver for this option expiry default is fkey if it exists
uPrcDriverKey_dy	TINYINT UNSIGNED		0	underlier price driver for this option expiry default is fkey if it exists
uPrcDriverKeyType	enum - SpdrKeyType		'None'	Stock or Future
uPrcBoundFKey_at	enum - AssetType		'None'	underlier price bounding future if any for this option expiry
uPrcBoundFKey_ts	enum - TickerSrc		'None'	underlier price bounding future if any for this option expiry
uPrcBoundFKey_tk	VARCHAR(12)		''	underlier price bounding future if any for this option expiry
uPrcBoundFKey_yr	SMALLINT UNSIGNED		0	underlier price bounding future if any for this option expiry
uPrcBoundFKey_mn	TINYINT UNSIGNED		0	underlier price bounding future if any for this option expiry
uPrcBoundFKey_dy	TINYINT UNSIGNED		0	underlier price bounding future if any for this option expiry
expiration	DATETIME(6)		'1900-01-01 00:00:00.000000'	option expiration date and time
maturityDate	DATETIME(6)		'1900-01-01 00:00:00.000000'
displayFactor	DOUBLE		0
cabPrice	DOUBLE		0
priceFormat	enum - PriceFormat		'None'
minTickSize	DOUBLE		0
timestamp	DATETIME(6)		'1900-01-01 00:00:00.000000'

PRIMARY KEY DEFINITION (Unique)

Field	Sequence
ekey_tk	1
ekey_yr	2
ekey_mn	3
ekey_dy	4
ekey_at	5
ekey_ts	6

CREATE TABLE EXAMPLE QUERY

CREATE TABLE `SRAnalytics`.`MsgOptExpiryDefinition` (
    `ekey_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None' COMMENT 'option root+expiration',
    `ekey_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None' COMMENT 'option root+expiration',
    `ekey_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'option root+expiration',
    `ekey_yr` SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'option root+expiration',
    `ekey_mn` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'option root+expiration',
    `ekey_dy` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'option root+expiration',
    `ticker_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None' COMMENT 'master ticker',
    `ticker_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None' COMMENT 'master ticker',
    `ticker_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'master ticker',
    `fkey_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None' COMMENT 'settlement future (if any)',
    `fkey_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None' COMMENT 'settlement future (if any)',
    `fkey_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'settlement future (if any)',
    `fkey_yr` SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'settlement future (if any)',
    `fkey_mn` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'settlement future (if any)',
    `fkey_dy` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'settlement future (if any)',
    `uPrcDriverKey_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None' COMMENT 'underlier price driver for this option expiry (default is fkey; if it exists)',
    `uPrcDriverKey_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None' COMMENT 'underlier price driver for this option expiry (default is fkey; if it exists)',
    `uPrcDriverKey_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'underlier price driver for this option expiry (default is fkey; if it exists)',
    `uPrcDriverKey_yr` SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'underlier price driver for this option expiry (default is fkey; if it exists)',
    `uPrcDriverKey_mn` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'underlier price driver for this option expiry (default is fkey; if it exists)',
    `uPrcDriverKey_dy` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'underlier price driver for this option expiry (default is fkey; if it exists)',
    `uPrcDriverKeyType` ENUM('None','Stock','Future','Option','MLeg') NOT NULL DEFAULT 'None' COMMENT 'Stock or Future',
    `uPrcBoundFKey_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None' COMMENT 'underlier price bounding future (if any) for this option expiry',
    `uPrcBoundFKey_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None' COMMENT 'underlier price bounding future (if any) for this option expiry',
    `uPrcBoundFKey_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'underlier price bounding future (if any) for this option expiry',
    `uPrcBoundFKey_yr` SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'underlier price bounding future (if any) for this option expiry',
    `uPrcBoundFKey_mn` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'underlier price bounding future (if any) for this option expiry',
    `uPrcBoundFKey_dy` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'underlier price bounding future (if any) for this option expiry',
    `expiration` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000' COMMENT 'option expiration date and time',
    `maturityDate` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000',
    `displayFactor` DOUBLE NOT NULL DEFAULT 0,
    `cabPrice` DOUBLE NOT NULL DEFAULT 0,
    `priceFormat` ENUM('None','N0','N1','N2','N3','N4','N5','N6','N7','F4','F8','Q8','F16','F32','H32','Q32','F64','H64','FullPenny','PartPenny','PartNickle','EQT','V1','V2','V3','V4','V5','V6','V7','V8','V9','V10','V11','V12','V13','V14','V15','A0','A1','A2','A3','A4','A5','A6','A7','E32') NOT NULL DEFAULT 'None',
    `minTickSize` DOUBLE NOT NULL DEFAULT 0,
    `timestamp` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000',
    PRIMARY KEY USING HASH (`ekey_tk`,`ekey_yr`,`ekey_mn`,`ekey_dy`,`ekey_at`,`ekey_ts`)
) ENGINE=SRSE DEFAULT CHARSET=LATIN1 COMMENT='This table maps option root/expiration combinations to their deliverable future.  Mappings are sourced fromm listing exchange product definitions.\nBaseObj:Root';

SELECT TABLE EXAMPLE QUERY

SELECT
    `ekey_at`,
    `ekey_ts`,
    `ekey_tk`,
    `ekey_yr`,
    `ekey_mn`,
    `ekey_dy`,
    `ticker_at`,
    `ticker_ts`,
    `ticker_tk`,
    `fkey_at`,
    `fkey_ts`,
    `fkey_tk`,
    `fkey_yr`,
    `fkey_mn`,
    `fkey_dy`,
    `uPrcDriverKey_at`,
    `uPrcDriverKey_ts`,
    `uPrcDriverKey_tk`,
    `uPrcDriverKey_yr`,
    `uPrcDriverKey_mn`,
    `uPrcDriverKey_dy`,
    `uPrcDriverKeyType`,
    `uPrcBoundFKey_at`,
    `uPrcBoundFKey_ts`,
    `uPrcBoundFKey_tk`,
    `uPrcBoundFKey_yr`,
    `uPrcBoundFKey_mn`,
    `uPrcBoundFKey_dy`,
    `expiration`,
    `maturityDate`,
    `displayFactor`,
    `cabPrice`,
    `priceFormat`,
    `minTickSize`,
    `timestamp`
FROM `SRAnalytics`.`MsgOptExpiryDefinition`
WHERE 
    /* Replace with a ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') */ 
    `ekey_at` = 'None'
  AND
    /* Replace with a ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') */ 
    `ekey_ts` = 'None'
  AND
    /* Replace with a VARCHAR(12) */ 
    `ekey_tk` = 'Example_ekey_tk'
  AND
    /* Replace with a SMALLINT UNSIGNED */ 
    `ekey_yr` = 123
  AND
    /* Replace with a TINYINT UNSIGNED */ 
    `ekey_mn` = 1
  AND
    /* Replace with a TINYINT UNSIGNED */ 
    `ekey_dy` = 1;

Doc Columns Query

SELECT * FROM SRAnalytics.doccolumns WHERE TABLE_NAME='OptExpiryDefinition' ORDER BY ordinal_position ASC;